-- FlowPlan数据库完整表结构和注释文档
-- 包含表结构定义、表注释和字段注释

-- ==========================================
-- 1. 组织表 (sys_org)
-- ==========================================

-- 表结构
CREATE TABLE sys_org (
    id BIGSERIAL PRIMARY KEY,
    org_name VARCHAR(100) NOT NULL,
    org_code VARCHAR(50) UNIQUE NOT NULL,
    parent_id BIGINT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES sys_org(id)
);

-- 表注释
COMMENT ON TABLE sys_org IS '组织架构表，存储公司或团队的组织结构信息';

-- 字段注释
COMMENT ON COLUMN sys_org.id IS '组织ID，主键，自增长';
COMMENT ON COLUMN sys_org.org_name IS '组织名称';
COMMENT ON COLUMN sys_org.org_code IS '组织编码，唯一标识符';
COMMENT ON COLUMN sys_org.parent_id IS '父组织ID，用于构建组织层级关系';
COMMENT ON COLUMN sys_org.create_time IS '创建时间';

-- ==========================================
-- 2. 用户表 (sys_user)
-- ==========================================

-- 表结构
CREATE TABLE sys_user (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'DEVELOPER',
    org_id BIGINT NOT NULL,
    avatar_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id) REFERENCES sys_org(id)
);

-- 表注释
COMMENT ON TABLE sys_user IS '用户信息表，存储系统用户的基本信息和权限';

-- 字段注释
COMMENT ON COLUMN sys_user.id IS '用户ID，主键，自增长';
COMMENT ON COLUMN sys_user.username IS '用户名，用于登录系统的唯一标识';
COMMENT ON COLUMN sys_user.email IS '邮箱地址，用户的联系邮箱';
COMMENT ON COLUMN sys_user.password_hash IS '密码哈希值，存储加密后的用户密码';
COMMENT ON COLUMN sys_user.role IS '用户角色，决定用户的权限级别';
COMMENT ON COLUMN sys_user.org_id IS '所属组织ID，关联sys_org表';
COMMENT ON COLUMN sys_user.avatar_url IS '用户头像URL地址';
COMMENT ON COLUMN sys_user.is_active IS '账户状态，标识用户账户是否激活';
COMMENT ON COLUMN sys_user.create_time IS '账户创建时间';
COMMENT ON COLUMN sys_user.update_time IS '账户信息更新时间';

-- ==========================================
-- 3. 项目表 (project)
-- ==========================================

-- 表结构
CREATE TABLE project (
    id BIGSERIAL PRIMARY KEY,
    project_name VARCHAR(200) NOT NULL,
    description TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'PLANNING',
    priority VARCHAR(10) DEFAULT 'MEDIUM',
    creator_id BIGINT NOT NULL,
    deadline TIMESTAMP,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (creator_id) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE project IS '项目信息表，存储所有项目的基本信息和状态';

-- 字段注释
COMMENT ON COLUMN project.id IS '项目ID，主键，自增长';
COMMENT ON COLUMN project.project_name IS '项目名称';
COMMENT ON COLUMN project.description IS '项目描述信息';
COMMENT ON COLUMN project.status IS '项目状态，标识项目的当前进展阶段';
COMMENT ON COLUMN project.priority IS '项目优先级';
COMMENT ON COLUMN project.creator_id IS '项目创建人ID，关联sys_user表';
COMMENT ON COLUMN project.deadline IS '项目截止时间';
COMMENT ON COLUMN project.create_time IS '项目创建时间';
COMMENT ON COLUMN project.update_time IS '项目信息更新时间';

-- ==========================================
-- 4. Excel计划表 (excel_plan)
-- ==========================================

-- 表结构
CREATE TABLE excel_plan (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    sheet_names JSONB,
    demander VARCHAR(100),
    upload_user_id BIGINT NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES project(id),
    FOREIGN KEY (upload_user_id) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE excel_plan IS 'Excel计划表，存储从Excel文件中提取的项目计划信息';

-- 字段注释
COMMENT ON COLUMN excel_plan.id IS 'Excel计划ID，主键，自增长';
COMMENT ON COLUMN excel_plan.project_id IS '关联项目ID，关联project表';
COMMENT ON COLUMN excel_plan.file_name IS 'Excel文件名';
COMMENT ON COLUMN excel_plan.file_path IS 'Excel文件存储路径';
COMMENT ON COLUMN excel_plan.file_size IS '文件大小，以字节为单位';
COMMENT ON COLUMN excel_plan.sheet_names IS 'Sheet页名称列表，JSON格式存储';
COMMENT ON COLUMN excel_plan.demander IS '需求提供方，说明需求的来源部门或人员';
COMMENT ON COLUMN excel_plan.upload_user_id IS '上传用户ID，关联sys_user表';
COMMENT ON COLUMN excel_plan.create_time IS '计划创建时间';
COMMENT ON COLUMN excel_plan.update_time IS '计划更新时间';

-- ==========================================
-- 5. 待办表 (todo)
-- ==========================================

-- 表结构
CREATE TABLE todo (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'TODO',
    priority VARCHAR(10) DEFAULT 'MEDIUM',
    todo_type VARCHAR(30) NOT NULL,
    assignee_id BIGINT,
    reporter_id BIGINT NOT NULL,
    due_date TIMESTAMP,
    estimated_hours DECIMAL(5,2),
    actual_hours DECIMAL(5,2),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES project(id),
    FOREIGN KEY (assignee_id) REFERENCES sys_user(id),
    FOREIGN KEY (reporter_id) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE todo IS '待办任务表，存储项目中的具体任务信息';

-- 字段注释
COMMENT ON COLUMN todo.id IS '待办任务ID，主键，自增长';
COMMENT ON COLUMN todo.project_id IS '关联项目ID，关联project表';
COMMENT ON COLUMN todo.title IS '待办任务标题';
COMMENT ON COLUMN todo.description IS '待办任务详细描述';
COMMENT ON COLUMN todo.status IS '待办任务状态';
COMMENT ON COLUMN todo.priority IS '待办任务优先级';
COMMENT ON COLUMN todo.todo_type IS '待办任务类型，区分不同性质的任务';
COMMENT ON COLUMN todo.assignee_id IS '任务负责人ID，关联sys_user表';
COMMENT ON COLUMN todo.reporter_id IS '任务报告人ID，关联sys_user表';
COMMENT ON COLUMN todo.due_date IS '任务截止日期';
COMMENT ON COLUMN todo.estimated_hours IS '预估工时，完成任务预计需要的时间';
COMMENT ON COLUMN todo.actual_hours IS '实际工时，完成任务实际花费的时间';
COMMENT ON COLUMN todo.create_time IS '任务创建时间';
COMMENT ON COLUMN todo.update_time IS '任务更新时间';

-- ==========================================
-- 6. Excel-待办关联表 (excel_todo_rel)
-- ==========================================

-- 表结构
CREATE TABLE excel_todo_rel (
    id BIGSERIAL PRIMARY KEY,
    excel_id BIGINT NOT NULL,
    todo_id BIGINT NOT NULL,
    relation_type VARCHAR(20) DEFAULT 'DERIVED',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (excel_id) REFERENCES excel_plan(id),
    FOREIGN KEY (todo_id) REFERENCES todo(id)
);

-- 表注释
COMMENT ON TABLE excel_todo_rel IS 'Excel计划与待办任务关联表，记录Excel计划与待办任务的对应关系';

-- 字段注释
COMMENT ON COLUMN excel_todo_rel.id IS '关联关系ID，主键，自增长';
COMMENT ON COLUMN excel_todo_rel.excel_id IS 'Excel计划ID，关联excel_plan表';
COMMENT ON COLUMN excel_todo_rel.todo_id IS '待办任务ID，关联todo表';
COMMENT ON COLUMN excel_todo_rel.relation_type IS '关联类型，标识Excel计划与待办任务的关系性质';
COMMENT ON COLUMN excel_todo_rel.create_time IS '关联关系创建时间';

-- ==========================================
-- 7. 笔记表 (note)
-- ==========================================

-- 表结构
CREATE TABLE note (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL,
    todo_id BIGINT,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    note_type VARCHAR(20) DEFAULT 'GENERAL',
    tags VARCHAR(500),
    author_id BIGINT NOT NULL,
    is_public BOOLEAN DEFAULT FALSE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES project(id),
    FOREIGN KEY (todo_id) REFERENCES todo(id),
    FOREIGN KEY (author_id) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE note IS '笔记表，存储项目和任务相关的笔记信息';

-- 字段注释
COMMENT ON COLUMN note.id IS '笔记ID，主键，自增长';
COMMENT ON COLUMN note.project_id IS '关联项目ID，关联project表';
COMMENT ON COLUMN note.todo_id IS '关联待办任务ID，关联todo表（可为空）';
COMMENT ON COLUMN note.title IS '笔记标题';
COMMENT ON COLUMN note.content IS '笔记内容，支持文本格式';
COMMENT ON COLUMN note.note_type IS '笔记类型，区分不同性质的笔记';
COMMENT ON COLUMN note.tags IS '笔记标签，用于分类和检索，逗号分隔';
COMMENT ON COLUMN note.author_id IS '笔记作者ID，关联sys_user表';
COMMENT ON COLUMN note.is_public IS '是否公开，标识笔记是否对所有项目成员可见';
COMMENT ON COLUMN note.create_time IS '笔记创建时间';
COMMENT ON COLUMN note.update_time IS '笔记更新时间';

-- ==========================================
-- 8. 审计日志表 (audit_log)
-- ==========================================

-- 表结构
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    operator_id BIGINT NOT NULL,
    operation_type VARCHAR(30) NOT NULL,
    resource_type VARCHAR(30) NOT NULL,
    resource_id BIGINT,
    old_value JSONB,
    new_value JSONB,
    ip_address INET,
    user_agent TEXT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (operator_id) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE audit_log IS '审计日志表，记录系统中的重要操作日志';

-- 字段注释
COMMENT ON COLUMN audit_log.id IS '日志ID，主键，自增长';
COMMENT ON COLUMN audit_log.operator_id IS '操作人ID，关联sys_user表';
COMMENT ON COLUMN audit_log.operation_type IS '操作类型，标识执行的操作种类';
COMMENT ON COLUMN audit_log.resource_type IS '资源类型，标识操作的对象类型';
COMMENT ON COLUMN audit_log.resource_id IS '资源ID，标识操作的具体对象';
COMMENT ON COLUMN audit_log.old_value IS '变更前的值，JSON格式存储';
COMMENT ON COLUMN audit_log.new_value IS '变更后的值，JSON格式存储';
COMMENT ON COLUMN audit_log.ip_address IS '操作发起的IP地址';
COMMENT ON COLUMN audit_log.user_agent IS '用户代理信息，记录客户端信息';
COMMENT ON COLUMN audit_log.create_time IS '日志创建时间';

-- ==========================================
-- 索引定义
-- ==========================================

-- 用户相关索引
CREATE INDEX idx_user_org_id ON sys_user(org_id);
CREATE INDEX idx_user_role ON sys_user(role);
CREATE UNIQUE INDEX uk_user_username ON sys_user(username);
CREATE UNIQUE INDEX uk_user_email ON sys_user(email);

-- 组织相关索引
CREATE UNIQUE INDEX uk_org_code ON sys_org(org_code);
CREATE INDEX idx_org_parent_id ON sys_org(parent_id);

-- 项目相关索引
CREATE INDEX idx_project_creator_id ON project(creator_id);
CREATE INDEX idx_project_status ON project(status);
CREATE INDEX idx_excel_plan_project_id ON excel_plan(project_id);
CREATE INDEX idx_project_deadline ON project(deadline) WHERE deadline IS NOT NULL;

-- Excel计划索引
CREATE INDEX idx_excel_plan_project_id ON excel_plan(project_id);
CREATE INDEX idx_excel_plan_upload_user ON excel_plan(upload_user_id);

-- 待办相关索引
CREATE INDEX idx_todo_project_status ON todo(project_id, status);
CREATE INDEX idx_todo_assignee_id ON todo(assignee_id);
CREATE INDEX idx_todo_type ON todo(todo_type);
CREATE INDEX idx_todo_due_date ON todo(due_date) WHERE due_date IS NOT NULL;
CREATE INDEX idx_todo_reporter_id ON todo(reporter_id);

-- Excel-待办关联索引
CREATE INDEX idx_excel_todo_excel_id ON excel_todo_rel(excel_id);
CREATE INDEX idx_excel_todo_todo_id ON excel_todo_rel(todo_id);

-- 笔记相关索引
CREATE INDEX idx_note_project_id ON note(project_id);
CREATE INDEX idx_note_todo_id ON note(todo_id);
CREATE INDEX idx_note_author_id ON note(author_id);
CREATE INDEX idx_note_tags ON note USING gin((string_to_array(tags, ',')));

-- 审计日志索引
CREATE INDEX idx_audit_log_operator_id ON audit_log(operator_id);
CREATE INDEX idx_audit_log_resource ON audit_log(resource_type, resource_id);
CREATE INDEX idx_audit_log_create_time ON audit_log(create_time);

-- 全文搜索索引  --失败
CREATE INDEX idx_project_fulltext ON project 
USING gin(to_tsvector('chinese', project_name || ' ' || COALESCE(description, '')));

CREATE INDEX idx_todo_fulltext ON todo 
USING gin(to_tsvector('chinese', title || ' ' || COALESCE(description, '')));

CREATE INDEX idx_note_fulltext ON note 
USING gin(to_tsvector('chinese', title || ' ' || content));

-- 说明: 创建自动更新时间戳的触发器

-- 创建自动更新时间戳的函数
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为需要自动更新时间戳的表创建触发器
CREATE TRIGGER update_sys_user_modtime 
    BEFORE UPDATE ON sys_user 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_project_modtime 
    BEFORE UPDATE ON project 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_todo_modtime 
    BEFORE UPDATE ON todo 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_excel_plan_modtime 
    BEFORE UPDATE ON excel_plan 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_note_modtime 
    BEFORE UPDATE ON note 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();